<?php
class NewsController extends BaseController {
    
    public $advertCode;
    
    private function setAdvert($catId) {
        $this->advertCode = Yii::app()->db->createCommand()->select('b.code')->from('banners_relations r')->join('banners b', 'b.bannerId=r.bannerId and b.type="Спонсор категории на главной" ')->where('r.catId='.$catId)->queryScalar();
    }
    
     public function actionAllNews($catId, $partnerUrl) {
        $errorModel = new ErrorReporting();
        if(isset($_POST['ErrorReporting'])) {
            if($errorModel->save()) $this->refresh();
        }
        $cat = Yii::app()->db->createCommand()->select('catId, name')->from('partners_cat')->where("catId=$catId")->queryRow();
        if(empty($cat)) throw new CHttpException(404, 'Не найдено указанной записи');
        $partner = Yii::app()->db->createCommand()->select('partnerid, url, about, name, type')->from('partners')->where("url=\"$partnerUrl\"")->queryRow();
        if(empty($partner)) throw new CHttpException(404, 'Не найдено указанной записи');
        $this->brending('partner', $partner['partnerid'], $catId);
        
        $relProfiles = Yii::app()->db->createCommand()->select('*')->from('partners_relprofiles')->where("partnerid=".$partner['partnerid'])->queryAll();
        $count = Yii::app()->db->createCommand()->select('count(*) as count')->from('news_relation')->where('partnerId='.$partner['partnerid'])->queryRow();
        $pagination = new CPagination($count['count']);
        $pagination->setPageSize(10);
        $subCats = Yii::app()->db->createCommand()->select('c.name, c.catId, c.parentId, rc.partnerId')->from('partners_sub_cat_rel rc')->join('partners_cat c', 'c.catId=rc.catId')->where('partnerid='.$partner['partnerid'])->queryAll();
        
        $news = Yii::app()->db->createCommand()->select('n.*')->from('news_relation nr')->join('news_data n', 'n.newsId=nr.newsId')->where('partnerId='.$partner['partnerid'])->limit($pagination->getLimit())->offset($pagination->getOffset())->queryAll();
        if(!Yii::app()->user->isGuest) {
            $userIsWork = Yii::app()->db->createCommand()->select('id')->from('partners_users')->where('userId='.Yii::app()->user->getId().' and partnerId='.$partner['partnerid'])->queryRow();
            if(!empty($news)) {
                $idArr = array();
                foreach ($news as $n) $idArr[] = $n['newsId'];
                $sql = 
                'SELECT 
                    nc.newsId, 
                    count(DISTINCT nc.comId) AS count 
                FROM `user_visits` `uv` 
                    LEFT JOIN `news_comments` `nc` ON nc.newsId = uv.typeId and uv.visitDate>nc.date
                WHERE uv.type="news" and uv.userId='.Yii::app()->user->getId().' and uv.typeId IN('.implode(',', $idArr).') 
                GROUP BY nc.newsId';
                $countReadCom = Yii::app()->db->createCommand($sql)->query()->readAll();
            }
        } 
        $advertCode = Yii::app()->db->createCommand()->select('b.code')->from('banners_relations r')->join('banners b', 'b.bannerId=r.bannerId and b.type="Спонсор категории на главной" ')->where('r.catId='.$cat['catId'])->queryScalar();
        $this->render('allNews', array('errorModel'=>$errorModel, 'advertCode'=>$advertCode, 'countReadCom'=>!empty($countReadCom) ? $countReadCom : '', 'news'=>$news, 'subCats'=>$subCats, 'relProfiles'=>$relProfiles, 'pagination'=>$pagination, 'partner'=>$partner, 'cat'=>$cat, 'userIsWork'=>!empty($userIsWork) ? $userIsWork : ''));
    }
    
    public function actionAllCatNews($catId) {
        $cat = Yii::app()->db->createCommand()->select('catId, name, about, parentId')->from('partners_cat')->where("catId=$catId")->queryRow();
        if(empty($cat)) throw new CHttpException(404, 'Не найдено указанной записи');
        $this->brending('cat', $catId);
        $count = Yii::app()->db->createCommand()->select('count(*) as count')->from('news_relation')->where('`partnerId`=0 and `catId`='.$catId)->queryRow();
        $pagination = new CPagination($count['count']);
        $pagination->setPageSize(10);
        
        $news = Yii::app()->db->createCommand()->select('n.*')->from('news_relation nr')->join('news_data n', 'n.newsId=nr.newsId')->where('`partnerId`=0 and `catId`='.$catId)->limit($pagination->getLimit())->offset($pagination->getOffset())->queryAll();
        if(!empty($news) && !Yii::app()->user->isGuest) {
            $idArr = array();
            foreach ($news as $n) $idArr[] = $n['newsId'];
            $sql = 
            'SELECT 
                nc.newsId, 
                count(DISTINCT nc.comId) AS count 
            FROM `user_visits` `uv` 
                LEFT JOIN `news_comments` `nc` ON nc.newsId = uv.typeId and uv.visitDate>nc.date
            WHERE uv.type="news" and uv.userId='.Yii::app()->user->getId().' and uv.typeId IN('.implode(',', $idArr).') 
            GROUP BY nc.newsId';
            $countReadCom = Yii::app()->db->createCommand($sql)->query()->readAll();
        }
        if($cat['parentId']) {
            $catSubCats = Yii::app()->db->createCommand()->select('catId, name, parentId')->from('partners_cat')->where('parentId='.$cat['parentId'])->queryAll();
        } else {
            $catSubCats = Yii::app()->db->createCommand()->select('catId, name, parentId')->from('partners_cat')->where('parentId='.$cat['catId'])->queryAll();
        }
        $this->setAdvert($cat['parentId'] ? $cat['parentId'] : $cat['catId']);
        $this->render('allCatNews', array('catSubCats'=>$catSubCats, 'news'=>$news, 'pagination'=>$pagination, 'cat'=>$cat, 'countReadCom'=>!empty($countReadCom) ? $countReadCom : ''));
    }
    
    public function actionCatNews($url, $catId) {
        $model = new NewsCommentForm();
        if(isset($_POST['NewsCommentForm'])) {
            if($model->validate()) {
                Yii::app()->db->createCommand()->insert('news_comments', CMap::mergeArray($_POST['NewsCommentForm'], array('userId'=>Yii::app()->user->getId(), 'date'=>time())));
                Yii::app()->db->createCommand('UPDATE `news_data` SET `countCom`=countCom+1 WHERE `newsId`='.$_POST['NewsCommentForm']['newsId'])->execute();
                $this->refresh();
            }
        }
        $cat = Yii::app()->db->createCommand()->select('catId, name, about, parentId')->from('partners_cat')->where("catId=$catId")->queryRow();
        if(empty($cat)) throw new CHttpException(404, 'Не найдено указанной записи');
        $this->brending('cat', $catId);
        $news = Yii::app()->db->createCommand()->select('*')->from('news_data')->where('url="'.$url.'"')->queryRow();
        if(empty($news)) throw new CHttpException(404, 'Не найдено указанной записи');
        $comments = Yii::app()->db->createCommand()->select('n.*, u.login, u.displayName, u.avatar, u.userid as comUid')->from('news_comments n')->join('users u', 'u.userId=n.userId')->where("newsId=".$news['newsId'])->order('date DESC')->queryAll();
        if(!Yii::app()->user->isGuest) {
            $visit = Yii::app()->db->createCommand()->select('id')->from('user_visits')->where('type="news" and typeId='.$news['newsId'].' and userId='.Yii::app()->user->getId())->queryRow();
            if(!empty($visit))
                Yii::app()->db->createCommand()->update('user_visits', array('visitDate'=>time()+10), 'type="news" and typeId='.$news['newsId'].' and userId='.Yii::app()->user->getId());
            else Yii::app()->db->createCommand()->insert('user_visits', array('type'=>'news', 'typeId'=>$news['newsId'], 'visitDate'=>time()+10, 'userId'=>Yii::app()->user->getId()));
        } 
        $this->setAdvert($cat['parentId'] ? $cat['parentId'] : $cat['catId']);
        if($cat['parentId']) {
            $catSubCats = Yii::app()->db->createCommand()->select('catId, name, parentId')->from('partners_cat')->where('parentId='.$cat['parentId'])->queryAll();
        } else {
            $catSubCats = Yii::app()->db->createCommand()->select('catId, name, parentId')->from('partners_cat')->where('parentId='.$catId)->queryAll();
        }
        $this->render('catNews', array('model'=>$model, 'cat'=>$cat, 'catSubCats'=>$catSubCats, 'news'=>$news, 'comments'=>$comments));
    }    
    
    public function actionNews($url, $catId, $partnerUrl) {
        $errorModel = new ErrorReporting();
        if(isset($_POST['ErrorReporting'])) {
            if($errorModel->save()) $this->refresh();
        }
        $model = new NewsCommentForm();
        if(isset($_POST['NewsCommentForm'])) {
            if($model->validate()) {
                Yii::app()->db->createCommand()->insert('news_comments', CMap::mergeArray($_POST['NewsCommentForm'], array('userId'=>Yii::app()->user->getId(), 'date'=>time())));
                Yii::app()->db->createCommand('UPDATE `news_data` SET `countCom`=countCom+1 WHERE `newsId`='.$_POST['NewsCommentForm']['newsId'])->execute();
                $this->refresh();
            }
        }
        $cat = Yii::app()->db->createCommand()->select('catId, name, parentId')->from('partners_cat')->where("catId=$catId")->queryRow();
        if(empty($cat)) throw new CHttpException(404, 'Не найдено указанной записи');
        $partner = Yii::app()->db->createCommand()->select('partnerid, url, about, name, type')->from('partners')->where("url=\"$partnerUrl\"")->queryRow();
        if(empty($partner)) throw new CHttpException(404, 'Не найдено указанной записи');
        $this->brending('partner', $partner['partnerid'], $catId);
        $news = Yii::app()->db->createCommand()->select('*')->from('news_data')->where('url="'.$url.'"')->queryRow();
        if(empty($news)) throw new CHttpException(404, 'Не найдено указанной записи');
        $subCats = Yii::app()->db->createCommand()->select('c.name, c.catId, c.parentId, rc.partnerId')->from('partners_sub_cat_rel rc')->join('partners_cat c', 'c.catId=rc.catId')->where('partnerid='.$partner['partnerid'])->queryAll();
        $relProfiles = Yii::app()->db->createCommand()->select('*')->from('partners_relprofiles')->where("partnerid=".$partner['partnerid'])->queryAll();
        $comments = Yii::app()->db->createCommand()->select('n.*, u.login, u.displayName, u.avatar, u.userid as comUid')->from('news_comments n')->join('users u', 'u.userId=n.userId')->where("newsId=".$news['newsId'])->order('date DESC')->queryAll();
        if(!empty($comments)) {
            $idArr = array();
            foreach ($comments as $c) {
                if(!in_array($c['comUid'], $idArr))
                    $idArr[] = $c['comUid'];
            }
            $sql = 
            'SELECT 
                nc.userId, 
                count(DISTINCT r.revId) AS reviews, 
                count(DISTINCT nc.comId) AS newsComments, 
                count(DISTINCT fc.comId) AS forumComments, 
                count(DISTINCT rc.comId) AS revComments, 
                count(DISTINCT bc.id) AS blogComments
            FROM `news_comments` `nc`
                LEFT JOIN `forum_comments` `fc` ON fc.userid = nc.userid
                LEFT JOIN `reviews` `r` ON r.userid = nc.userid
                LEFT JOIN `blog_comments` `bc` ON bc.userid = nc.userid
                LEFT JOIN `reviews_comments` `rc` ON bc.userid = nc.userid
            WHERE nc.userid IN('.implode(',', $idArr).') GROUP BY nc.userId';
            $countMessagesData = Yii::app()->db->createCommand($sql)->query()->readAll();
        }
        if(!Yii::app()->user->isGuest) {
            $userIsWork = Yii::app()->db->createCommand()->select('id')->from('partners_users')->where('userId='.Yii::app()->user->getId().' and partnerId='.$partner['partnerid'])->queryRow();
            $visit = Yii::app()->db->createCommand()->select('id')->from('user_visits')->where('type="news" and typeId='.$news['newsId'].' and userId='.Yii::app()->user->getId())->queryRow();
            if(!empty($visit))
                Yii::app()->db->createCommand()->update('user_visits', array('visitDate'=>time()+10), 'type="news" and typeId='.$news['newsId'].' and userId='.Yii::app()->user->getId());
            else Yii::app()->db->createCommand()->insert('user_visits', array('type'=>'news', 'typeId'=>$news['newsId'], 'visitDate'=>time()+10, 'userId'=>Yii::app()->user->getId()));
        } 
        $advertCode = Yii::app()->db->createCommand()->select('b.code')->from('banners_relations r')->join('banners b', 'b.bannerId=r.bannerId and b.type="Спонсор категории на главной" ')->where('r.catId='.$cat['catId'])->queryScalar();
        $this->render('news', array('errorModel'=>$errorModel, 'advertCode'=>$advertCode, 'countMessagesData'=>!empty($countMessagesData) ? $countMessagesData : 0, 'subCats'=>$subCats, 'model'=>$model, 'cat'=>$cat, 'partner'=>$partner, 'comments'=>$comments, 'news'=>$news, 'userIsWork'=>!empty($userIsWork) ? $userIsWork : '', 'relProfiles'=>$relProfiles));
    }
}